﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Microsoft.ApplicationBlocks.Data;

using YouHP.IDAL;
using YouHP.Model;
using YouHP.Model.Group;


namespace YouHP.SqlServerDAL
{
    public class DiseaseDAL : IDisease
    {
        #region 疾病添加删除修改
        public bool AddDisease(DiseaseInfo info)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(Common.ConnStr);

            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                /*疾病添加*/
                SqlParameter[] outParms = new SqlParameter[]  {
                    new SqlParameter("@name", SqlDbType.NVarChar, 50),
                    new SqlParameter("@pinyin", SqlDbType.NVarChar, 50),
                    new SqlParameter("@diseasetypeid", SqlDbType.Int),
                    new SqlParameter("@introduction", SqlDbType.NVarChar, 4000),
                    new SqlParameter("@description", SqlDbType.NText),
                    new SqlParameter("@uploaddate", SqlDbType.DateTime),
                    new SqlParameter("@ClassPatientId", SqlDbType.Int),
                    new SqlParameter("@PatientCount", SqlDbType.Int), 
                    new SqlParameter("@id", SqlDbType.Int)
                };
                outParms[0].Value = info.Name;
                outParms[1].Value = info.PinYin;
                outParms[2].Value = info.Type;
                outParms[3].Value = info.Intro;
                outParms[4].Value = info.Desp;
                outParms[5].Value = info.UpdateTime;
                outParms[6].Value = info.PatientId;
                outParms[7].Value = info.PatientCount;
                outParms[8].Direction = ParameterDirection.Output;

                /*根据疾病类型找到对应的圈子类型信息*/
                GroupTypeInfo gtInfo = GroupTypeDAL.GetGroupTypeInfoByDiseaseType(info.Type);
                if (gtInfo == null)
                {
                    ts.Rollback();
                    retVal = false;
                }

                SqlHelper.ExecuteNonQuery(ts, CommandType.StoredProcedure, Common.DiseaseSP_AddDisease, outParms);
                if (outParms[8].Value != null && outParms[8].Value.ToString().Length > 0)
                {
                    /*论坛板块(大圈子)添加*/
                    GroupInfo groupInfo = new GroupInfo();
                    groupInfo.DiseaseId = Convert.ToInt32(outParms[8].Value.ToString());
                    groupInfo.GroupName = info.Name;
                    groupInfo.GroupTypeName = gtInfo.GroupTypeName;
                    groupInfo.GroupType = gtInfo.Id;     /*系统圈子，自建圈子类型设置为0*/
                    groupInfo.CreatTime = DateTime.Now;

                    SqlHelper.ExecuteNonQuery(ts, Common.GroupSP_AddGroup,
                        groupInfo.GroupName,
                        groupInfo.GroupType,
                        groupInfo.GroupTypeName,
                        groupInfo.DiseaseId,
                        groupInfo.TreatmentId,
                        groupInfo.SymptomId,
                        groupInfo.LabcheckId,
                        groupInfo.Area,
                        groupInfo.ProvinceId,
                        groupInfo.CityId,
                        groupInfo.ProvinceName,
                        groupInfo.CityName,
                        groupInfo.Keyword,
                        groupInfo.Introduction,
                        groupInfo.Logo,
                        groupInfo.Moderators,
                        groupInfo.AppUserId,
                        groupInfo.AppUserName,
                        groupInfo.UserNum,
                        groupInfo.TopicNum,
                        groupInfo.ReplyNum,
                        groupInfo.PostNum,
                        groupInfo.LimitUser,
                        groupInfo.LastPostTime,
                        groupInfo.IsGoodGroup,
                        groupInfo.Hits,
                        groupInfo.CreatTime,
                        groupInfo.BrowserPermission,
                        groupInfo.JoinPermission,
                        groupInfo.IsValid,
                        groupInfo.TopicSortString
                        );
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        public bool UpdateDisease(DiseaseInfo diseaseInfo)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(Common.ConnStr);

            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                /*更新疾病信息*/
                SqlHelper.ExecuteNonQuery(Common.ConnStr,
                    Common.DiseaseSP_UpdateDisease,
                    diseaseInfo.Id, diseaseInfo.Name, diseaseInfo.PinYin, diseaseInfo.Type,
                    diseaseInfo.Intro, diseaseInfo.Desp, diseaseInfo.UpdateTime,
                    diseaseInfo.PatientId,
                    diseaseInfo.PatientName);

                /*更新疾病对应的圈子信息*/
                //目前暂不实现，需手动更新

                ts.Commit();
                retVal = true;
            }
            catch (Exception ex)
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        public bool DeleteDisease(int[] dszitemids)
        {
            bool retVal = false;
            string tmpCond = "";
            int cnt = dszitemids.Length;
            if (cnt <= 0)
                return false;

            StringBuilder sb = new StringBuilder();
            sb.Append(" in (");
            sb.Append(dszitemids[0]);

            for (int i = 1; i < cnt; i++)
            {
                sb.Append(", ");
                sb.Append(dszitemids[i]);
            }
            sb.Append(")");

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                /*1.删除圈子对应的回复信息*/
                tmpCond = " [Id] in (SELECT [Reply].[Id] FROM [GROUP], [REPLY] "
                    + " WHERE [REPLY].[GroupId] = [GROUP].[Id] AND [GROUP].[DiseaseId] "
                    + sb.ToString() + " ) ";
                SqlHelper.ExecuteNonQuery(ts, Common.ReplySP_DelReply, tmpCond);

                /*2.删除圈子对应的主题信息*/
                tmpCond = " [Id] in (SELECT [Topic].[Id] FROM [GROUP], [Topic] "
                    + " WHERE [Topic].[GroupId] = [GROUP].[Id] AND [GROUP].[DiseaseId] "
                    + sb.ToString() + " ) ";
                SqlHelper.ExecuteNonQuery(ts, Common.TopicSP_DelTopic, tmpCond);

                /*3.删除圈子信息(大圈子)*/
                SqlHelper.ExecuteNonQuery(ts, Common.GroupSP_DelGroup, " [DiseaseId] " + sb.ToString() + " and [GroupType] > 0 ");

                /*4.删除疾病信息*/
                SqlHelper.ExecuteNonQuery(ts, Common.DiseaseSP_DelDisease, " [id] " + sb.ToString());

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        public DiseaseInfo GetDiseaseInfo(int id)
        {
            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.GetFullName("GetDisease"),
                id) as IDataReader;

            return CBO.FillObject<DiseaseInfo>(dr);
        }

        public int GetDiseaseIdByName(string diseaseName)
        {
            int diseaseId = -1;
            SqlDataReader rdr = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.DiseaseSP_GetDiseaseIdByName,
                diseaseName);

            if (rdr != null && rdr.Read())
            {
                diseaseId = rdr.GetInt32(0);
            }
            rdr.Close();

            return diseaseId;
        }

        #endregion

        #region 疾病列表
        private List<DiseaseInfo> GetDiseaseList(int pageSize, int pageIndex, string condStr, string orderStr, out int count)
        {
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.DiseaseSP_GetDiseaseList,
                pageSize, pageIndex, condStr, orderStr);

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
            }
            else
            {
                count = 0;
            }
            dr.Close();

            dr = dset.Tables[1].CreateDataReader();
            return CBO.FillCollection<DiseaseInfo>(dr);
        }

        private List<DiseaseInfo> GetDiseaseListWithPatientInfo(int pageSize, int pageIndex, string condStr, string orderStr, out int count)
        {
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.DiseaseSP_GetdiseaseListWithPatientInfo,
                pageSize, pageIndex, condStr, orderStr);

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
            }
            else
            {
                count = 0;
            }
            dr.Close();

            dr = dset.Tables[1].CreateDataReader();
            return CBO.FillCollection<DiseaseInfo>(dr);
        }

        private List<DiseaseInfo> GetTopDiseases(string condStr, string orderStr, int n)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.DiseaseSP_GetTopDiseases,
                n, condStr, orderStr) as IDataReader;
            return CBO.FillCollection<DiseaseInfo>(reader);
        }

        public List<SymptomAndIncidenceCount> GetHighIncidenceSymptomsOfDisease(int diseaseId, int count)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.DiseaseSP_GetHighIncidenceSymptomsOfDisease,
                diseaseId, count) as IDataReader;
            return CBO.FillCollection<SymptomAndIncidenceCount>(reader);
        }

        // 获得治疗该疾病的几个常用治疗
        public List<TreatmentAndUsedCount> GetMostFrequentUsedTreatmentsOfDisease(int diseaseId, int count)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.DiseaseSP_GetMostFrequentUsedTreatmentsOfDisease,
                diseaseId, count) as IDataReader;
            return CBO.FillCollection<TreatmentAndUsedCount>(reader);
        }

        // 获得患此种疾病的病人的按地区分布的统计列表
        public List<ProvinceAndPatientCount> GetPatientLocationDistributionStatOfDisease(int diseaseId, int count)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.DiseaseSP_GetPatientLocationDistributionStatOfDisease,
                diseaseId, count) as IDataReader;
            return CBO.FillCollection<ProvinceAndPatientCount>(reader);
        }

        public List<DiseaseInfo> GetDiseaseList(int pageSize, int pageIndex, string condStr, out int count)
        {
            return GetDiseaseList(pageSize, pageIndex, condStr, " [Disease].[PatientCount] desc ", out count);
        }

        public List<DiseaseInfo> GetDiseaseListWithPatientInfo(int pageSize, int pageIndex, string condStr, out int count)
        {
            return GetDiseaseListWithPatientInfo(pageSize, pageIndex, condStr, " [Disease].[PatientCount] desc ", out count);
        }

        public List<DiseaseInfo> GetHottestDiseases(int n)
        {
            return GetTopDiseases("[DiseaseName]<>'其他'", " [Disease].[PatientCount] desc ", n);
        }

        /*按照疾病中文名来检索疾病*/
        public List<DiseaseInfo> GetDiseaseByName(int pageSize, int pageIndex, string keyword, out int count)
        {
            string condstr = string.Format(@" [diseasename] like '%{0}%' ",
                keyword.Replace(@"'", @"''"));
            return GetDiseaseList(pageSize, pageIndex, condstr, " [Disease].[PatientCount] desc ", out count);
        }

        /*按照疾病名称拼音字母全称来检索疾病（前台检索）*/
        public List<DiseaseInfo> GetDiseaseByPinYin(int pageSize, int pageIndex, string firstLetter, out int count)
        {
            string condstr = string.Format(@" [firstsymbolofname] like '{0}%' ", firstLetter.ToUpper());
            return GetDiseaseListWithPatientInfo(pageSize, pageIndex, condstr, " [Disease].[PatientCount] desc ", out count);
        }

        /*按照疾病类型来检索疾病*/
        public List<DiseaseInfo> GetDiseaseByType(int pageSize, int pageIndex, int type, out int count)
        {
            string condstr = string.Format(" [diseasetypeid]={0} ", type);
            return GetDiseaseList(pageSize, pageIndex, condstr, " [Disease].[PatientCount] desc ", out count);

        }
        #endregion

        #region 疾病所相关症状增加删除操作

        /*采用批处理方式来批量增加疾病的相关症状*/
        public bool AddDiseaseSymptom(int diseaseId, List<DiseaseSymptomInfo> symptomList)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(Common.ConnStr);

            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                foreach (DiseaseSymptomInfo info in symptomList)
                {
                    SqlHelper.ExecuteNonQuery(ts,
                        Common.DiseaseSP_AddDiseaseSymptom,
                        diseaseId, info.SymptomId, info.Type);
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        public bool UpdateDiseaseSymptom(int diseaseId, int type, List<DiseaseSymptomInfo> symptomList)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(Common.ConnStr);

            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                /*删除原来的关联数据*/
                StringBuilder sb = new StringBuilder();
                sb.Append("[diseaseid]=");
                sb.Append(diseaseId);
                sb.Append(" and [type]=");
                sb.Append(type);
                SqlHelper.ExecuteNonQuery(ts, Common.DiseaseSP_DelDiseaseSymptoms, sb.ToString());

                /*插入新的关联数据*/
                foreach (DiseaseSymptomInfo info in symptomList)
                {
                    SqlHelper.ExecuteNonQuery(ts,
                        Common.DiseaseSP_AddDiseaseSymptom,
                        diseaseId, info.SymptomId, info.Type);
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        /*根据疾病症状关联表id删除纪录*/
        public bool DelDiseaseSymptoms(int[] dsIds)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(Common.ConnStr);

            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                int cnt = dsIds.Length;
                if (cnt <= 0)
                    return false;

                StringBuilder sb = new StringBuilder();
                sb.Append("[id] in (");
                sb.Append(dsIds[0]);

                for (int i = 1; i < cnt; i++)
                {
                    sb.Append(", ");
                    sb.Append(dsIds[i]);
                }
                sb.Append(")");

                SqlHelper.ExecuteNonQuery(ts,
                     Common.DiseaseSP_DelDiseaseSymptoms,
                     sb.ToString());

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        /*获取疾病相关的所有症状列表，包括主要和次要*/
        public List<DiseaseSymptomInfo> GetDiseaseSymptomList(int diseaseId)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                        Common.DiseaseSP_GetDiseaseSymptomList,
                        diseaseId);
            return CBO.FillCollection<DiseaseSymptomInfo>(reader);
        }


        /*获取疾病相关的某种类型的症状列表*/
        public List<DiseaseSymptomInfo> GetDiseaseSymptomList(int diseaseId, int type)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                        Common.DiseaseSP_GetDiseaseSymptomListByType,
                        diseaseId, type);
            return CBO.FillCollection<DiseaseSymptomInfo>(reader);
        }

        /*获取可选的症状列表（如：type=0，则表示次要症状的可选集*/
        public List<SymptomInfo> GetSelectedSymptomCollect(int diseaseId, int type)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                        Common.DiseaseSP_GetSelectedSymptomCollect,
                        diseaseId, type);
            return CBO.FillCollection<SymptomInfo>(reader);
        }
        #endregion

        #region 疾病所相关治疗增加删除操作
        /*采用批处理方式来批量增加疾病的相关治疗*/
        public bool AddDiseaseTreatment(int diseaseId, List<DiseaseTreatmentInfo> treatmentList)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(Common.ConnStr);

            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                foreach (DiseaseTreatmentInfo info in treatmentList)
                {
                    SqlHelper.ExecuteNonQuery(ts,
                        Common.DiseaseSP_AddDiseaseTreatment,
                        diseaseId, info.TreatmentId, info.Type);
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        /*采用批处理方式来更新疾病的相关症状，说明：先删除老的数据，再添加新的数据*/
        public bool UpdateDiseaseTreatment(int diseaseId, List<DiseaseTreatmentInfo> treatmentList)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(Common.ConnStr);

            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                /*删除原来的关联数据*/
                StringBuilder sb = new StringBuilder();
                sb.Append("[diseaseid]=");
                sb.Append(diseaseId);
                SqlHelper.ExecuteNonQuery(ts, Common.DiseaseSP_DelDiseaseTreatments, sb.ToString());

                /*插入新的关联数据*/
                foreach (DiseaseTreatmentInfo info in treatmentList)
                {
                    SqlHelper.ExecuteNonQuery(ts,
                        Common.DiseaseSP_AddDiseaseTreatment,
                        diseaseId, info.TreatmentId, info.Type);
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        /*根据疾病治疗关联表id删除纪录*/
        public bool DelDiseaseTreatments(int[] dtIds)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(Common.ConnStr);

            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                int cnt = dtIds.Length;
                if (cnt <= 0)
                    return false;

                StringBuilder sb = new StringBuilder();
                sb.Append("[id] in (");
                sb.Append(dtIds[0]);

                for (int i = 1; i < cnt; i++)
                {
                    sb.Append(", ");
                    sb.Append(dtIds[i]);
                }
                sb.Append(")");

                SqlHelper.ExecuteNonQuery(ts,
                     Common.DiseaseSP_DelDiseaseTreatments,
                     sb.ToString());

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        /*获取疾病相关的所有治疗列表*/
        public List<DiseaseTreatmentInfo> GetDiseaseTreatmentList(int diseaseId)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                        Common.DiseaseSP_GetDiseaseTreatmentList,
                        diseaseId);
            return CBO.FillCollection<DiseaseTreatmentInfo>(reader);
        }
        /*获取治疗相关的所有疾病列表*/
        public List<DiseaseInfo> GetDiseaseTreatmentListByTreatmentId(int treatmentId, int n)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                        Common.DiseaseSP_GetDiseaseTreatmentListByTreatmentId,
                        treatmentId, n);
            return CBO.FillCollection<DiseaseInfo>(reader);
        }
        public List<DiseaseInfo> GetDiseaseListBySymptomId(int symptomId, int n)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                                 Common.SymptomSP_GetDiseaseListBySymptomId,
                                 symptomId, n);
            return CBO.FillCollection<DiseaseInfo>(reader);
        }
        #endregion

        #region 疾病所相关实验室指标增加删除操作

        /*采用批处理方式来批量增加疾病的相关实验室指标*/
        public bool AddDiseaseLabcheck(int diseaseId, List<DiseaseLabcheckInfo> labcheckList)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                foreach (DiseaseLabcheckInfo info in labcheckList)
                {
                    SqlHelper.ExecuteNonQuery(ts,
                        Common.DiseaseSP_AddDiseaseLabcheck,
                        diseaseId, info.LabcheckId, info.Type, info.ChartIndex, info.AxisIndex);
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        /*根据疾病实验室指标关联表id删除纪录*/
        public bool DelDiseaseLabchecks(int[] dlIds)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(Common.ConnStr);

            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                int cnt = dlIds.Length;
                if (cnt <= 0)
                    return false;

                StringBuilder sb = new StringBuilder();
                sb.Append("[id] in (");
                sb.Append(dlIds[0]);

                for (int i = 1; i < cnt; i++)
                {
                    sb.Append(", ");
                    sb.Append(dlIds[i]);
                }
                sb.Append(")");

                SqlHelper.ExecuteNonQuery(ts,
                     Common.DiseaseSP_DelDiseaseLabchecks,
                     sb.ToString());

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }

            conn.Close();

            return retVal;
        }

        /*获取疾病相关的所有实验室指标列表*/
        public List<DiseaseLabcheckInfo> GetDiseaseLabcheckList(int dszId)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                        Common.DiseaseSP_GetDiseaseLabcheckList,
                        dszId);
            return CBO.FillCollection<DiseaseLabcheckInfo>(reader);
        }

        /*采用批处理方式来更新疾病的相关实验室指标，说明：先删除老的数据，再添加新的数据*/
        public bool UpdateDiseaseLabcheck(int diseaseId, List<DiseaseLabcheckInfo> labcheckList)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                /*删除原来的关联数据*/
                StringBuilder sb = new StringBuilder();
                sb.Append("[diseaseid]=");
                sb.Append(diseaseId);
                SqlHelper.ExecuteNonQuery(ts, Common.DiseaseSP_DelDiseaseLabchecks, sb.ToString());

                /*插入新的关联数据*/
                foreach (DiseaseLabcheckInfo info in labcheckList)
                {
                    SqlHelper.ExecuteNonQuery(ts,
                        Common.DiseaseSP_AddDiseaseLabcheck,
                        diseaseId, info.LabcheckId, info.Type, info.ChartIndex, info.AxisIndex);
                }

                ts.Commit();
                retVal = true;
            }
            catch (Exception ex)
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        #endregion

        #region 疾病类型
        public bool AddDiseaseType(DiseaseTypeInfo info)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(Common.ConnStr);

            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                /*疾病类型添加*/
                SqlParameter[] outParms = new SqlParameter[]  {
                    new SqlParameter("@name", SqlDbType.NVarChar, 50),
                    new SqlParameter("@parentid", SqlDbType.Int),
                    new SqlParameter("@id", SqlDbType.Int),
                };
                outParms[0].Value = info.Name;
                outParms[1].Value = info.ParentId;
                outParms[2].Direction = ParameterDirection.Output;

                SqlHelper.ExecuteNonQuery(ts, CommandType.StoredProcedure,
                    Common.DiseaseSP_AddDiseaseType,
                    outParms);

                if (outParms[2].Value != null && outParms[2].Value.ToString().Length > 0)
                {
                    /*论坛板块(大圈子)添加*/
                    GroupTypeInfo groupTypeInfo = new GroupTypeInfo();
                    groupTypeInfo.DiseaseType = Convert.ToInt32(outParms[2].Value.ToString());
                    groupTypeInfo.GroupTypeName = info.Name;

                    SqlHelper.ExecuteNonQuery(ts, Common.GroupTypeSP_AddGroupType,
                        groupTypeInfo.GroupTypeName,
                        groupTypeInfo.DiseaseType,
                        groupTypeInfo.Introduction,
                        groupTypeInfo.Logo,
                        groupTypeInfo.GroupNum
                        );
                }

                ts.Commit();
                retVal = true;
            }
            catch (Exception ex)
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        public bool DelDiseaseType(int[] itemIds)
        {
            bool retVal = false;
            int cnt = itemIds.Length;
            if (cnt <= 0)
                return false;

            StringBuilder sb = new StringBuilder();
            sb.Append(" in (");
            sb.Append(itemIds[0]);

            for (int i = 1; i < cnt; i++)
            {
                sb.Append(", ");
                sb.Append(itemIds[i]);
            }
            sb.Append(") ");

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                /*1.删除圈子类型信息*/
                SqlHelper.ExecuteNonQuery(ts, Common.GroupTypeSP_DelGroupType, " [DiseaseType] " + sb.ToString());
                /*2.删除疾病类型信息*/
                SqlHelper.ExecuteNonQuery(ts, Common.DiseaseSP_DelDiseaseType, " [id] " + sb.ToString());

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        public bool UpdateDiseaseType(DiseaseTypeInfo info)
        {
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.GetFullName(Common.DiseaseSP_UpdateDiseaseType),
                info.Id, info.Name, info.ParentId);
            return (cnt > 0);
        }

        public DiseaseTypeInfo GetDiseaseType(int typeId)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.GetFullName(Common.DiseaseSP_GetDiseaseType),
                typeId);
            return CBO.FillObject<DiseaseTypeInfo>(reader);
        }

        public List<DiseaseTypeInfo> GetDiseaseTypesList(int parentId)
        {
            string condstr = string.Format(" [parentid]={0} ", parentId);
            return GetDiseaseTypesList(condstr);
        }

        public List<DiseaseTypeInfo> GetDiseaseTypesList()
        {
            return GetDiseaseTypesList("");
        }

        private List<DiseaseTypeInfo> GetDiseaseTypesList(string condstr)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.GetFullName(Common.DiseaseSP_GetDiseaseTypeList),
                condstr);
            return CBO.FillCollection<DiseaseTypeInfo>(reader);
        }
        #endregion

        #region 疾病图表设置

        public DiseaseGraphSettingInfo GetDiseaseGraphSettingInfo(int diseaseId)
        {
            IDataReader reader = SqlHelper.ExecuteReader(
                Common.ConnStr,
                Common.DiseaseSP_GetDiseaseGraphSettingInfo,
                diseaseId);
            return CBO.FillObject<DiseaseGraphSettingInfo>(reader);
        }

        public bool AddDiseaseGraphSetting(DiseaseGraphSettingInfo info)
        {
            int cnt = SqlHelper.ExecuteNonQuery(
                Common.ConnStr,
                Common.DiseaseSP_AddDiseaseGraphSetting,
                info.DiseaseId,
                info.GraphGeneralSetting, info.GraphLabcheckSetting,
                info.GraphSymptomSetting, info.GraphTreatmentSetting);
            return cnt > 0;
        }

        public bool UpdateDiseaseGraphSetting(DiseaseGraphSettingInfo info)
        {
            int cnt = SqlHelper.ExecuteNonQuery(
                 Common.ConnStr,
                 Common.DiseaseSP_UpdateDiseaseGraphSetting,
                 info.Id, info.DiseaseId,
                 info.GraphGeneralSetting, info.GraphLabcheckSetting,
                 info.GraphSymptomSetting, info.GraphTreatmentSetting);
            return cnt > 0;
        }

        #endregion

        #region 疾病统计
        public List<DiseaseStatRegionInfo> GetDiseaseStatRegion(int did, int regionId)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.DiseaseSP_GetDiseaseStatRegion,
                did, regionId) as IDataReader;
            return CBO.FillCollection<DiseaseStatRegionInfo>(reader);
        }

        public int GetDiseaseStatAge(int did, int startAge, int endAge)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.DiseaseSP_GetDiseaseStatAge,
                did,
                startAge,
                endAge
                ) as IDataReader;

            return 0;
        }

        public int GetDiseaseStatDuration(int did, int startPoint, int endPoint)
        {
            int count = 0;

            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
               Common.DiseaseSP_GetDiseaseStatDuration,
               did,
               startPoint,
               endPoint
               ) as IDataReader;

            if (reader != null && reader.Read())
            {
                count = reader.GetInt32(0);
            }
            reader.Close();

            return count;
        }

        public List<DiseaseStatSymptomInfo> GetDiseaseStatSymptom(int did)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
               Common.DiseaseSP_GetDiseaseStatSymptom,
               did) as IDataReader;
            return CBO.FillCollection<DiseaseStatSymptomInfo>(reader);
        }

        public List<DiseaseStatTreamentInfo> GetDiseaseStatTreatment(int did)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.DiseaseSP_GetDiseaseStatTreatment,
                did) as IDataReader;
            return CBO.FillCollection<DiseaseStatTreamentInfo>(reader);
        }
        #endregion
    }
}
